I'm pretty new to App Script, so this is a real head-scratcher for me.
With four named ranges on Sheet1 ("Range1","Range2","Range3","Range4")
Logger.log(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getNamedRanges());
Returns [NamedRange, NamedRange, NamedRange, NamedRange]
The larger goal has been to get this script working, to return the named range an active cell resides in.
Trying to get that working and debugging lead me to the above, making me wonder if the script isn't working due to how getNamedRanges is being returned?
Test Sheet file here.
Is the [NamedRange] result expected behavior or am I missing something to get the names of the ranges to return?
Thanks!
It is expected behavior. When logging a object of a class, the name of the class is usually logged in the console. .getNamedRanges() returns a array[] of namedRange objects.
console.log(
SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName("Sheet1")
.getNamedRanges()[0]/*First namedRange*/
)//Expected: NamedRange Type: Object
Each namedRange has many inbuilt functions, i.e., methods. A list of such methods is here. One such method is getName(). You can use it to get the name of the NamedRange object:
console.log(
SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName("Sheet1")
.getNamedRanges()[0]/*First namedRange*/
.getName()/*First named range's name*/
)//Expected: "Range1" Type:string
You may use Array.map to get all the names:
console.log(
SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName("Sheet1")
.getNamedRanges()
.map(namedRange => namedRange.getName())
)//Expected: ["Range1","Range2","Range3","Range4"] Type: Object(specifically a array of strings)
getNamedRanges() returns an array of NamedRanges.
let namedRanges = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getNamedRanges());
namedRanges.forEach( namedRange => Logger.log(namedRange.getRange().getA1Notation()) );
will give a list of ranges in A1 notation such as
A1:B10
C2:Z100
function getSpreadsheetsNamedRanges() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Sheet0");
const nrA = ss.getNamedRanges().map(rg => [rg.getName(),rg.getRange().getSheet().getName(),rg.getRange().getA1Notation()])
sh.clearContents();
nrA.unshift(["Range Name","Sheet Name","A1 Notation"]);
sh.getRange(1,1,nrA.length,nrA[0].length).setValues(nrA);
}